﻿CREATE Procedure state_BatchArrivedMail
	@BatchID int,
	@StatusToID int = 0
As

DECLARE @Subject varchar(512), @Text nvarchar(4000), @To nvarchar(1024)


SELECT
	@Subject = dbo.sprint7('Batch #%s for [%s] (%s loans) was sent to [%s] from [%s] at %s by %s.',
PB.ID, PB.ContractHolderNick, PB.LoansCount, PB.Status, S.Name, CONVERT(varchar,PB.StatusDate,120), PB.StatusUser
),
@Text = dbo.Config_ShowPurchaseBatch(PB.ID),
@To = CONVERT(nvarchar(1024),S_P.StateParameterValue)
FROM         v_PurchaseBatch AS PB INNER JOIN
                      tlog_State AS L ON PB.StatusID = L.StateTo AND PB.ID = L.TablePK INNER JOIN
                      t_State AS S ON L.StateFrom = S.ID
INNER JOIN t_State_Parameters S_P ON PB.StatusID = S_P.StateID
WHERE PB.ID = @BatchID AND
			L.IsLast = 1 AND
			NOT CONVERT(nvarchar(1024),S_P.StateParameterValue) LIKE '-%' AND
			@StatusToID IN (PB.StatusID,0)

--PRINT @Subject+':'+@To
IF ISNULL(@To,'') = '' RETURN

EXEC msdb.dbo.sp_send_dbmail @profile_name='FQuest Processing', @recipients=@To,
@subject=@Subject,@body=@Text,@body_format='HTML',@importance='High'

/*
SELECT @Subject = dbo.sprintf3('Loan $0 ($1) arrived in $2 from $3 .', v_LoanInfo.LoanID, 
               t_Borrower.FirstName + ' ' + t_Borrower.LastName, MenuPath,dbo.MenuPathByID(dbo.Loan_Menus_Prev(@LoanID))),
               @Text = dbo.sprintf1('Menu:$1'+CHAR(13)+dbo.config_URL_ShowLoan()+'$0', v_LoanInfo.LoanID,MenuPath)
FROM  v_LoanInfo
INNER JOIN t_Loan ON v_LoanInfo.LoanID = t_Loan.ID
INNER JOIN t_Borrower ON t_Borrower.LoanID = t_Loan.ID
INNER JOIN v_Menu ON t_Loan.MenuID = v_Menu.MenuID
WHERE (t_Loan.ID = @LoanID) AND t_Borrower.Pos = 0

EXEC s_Mail @User,@Subject,@Text
*/




